*************************************************************************************
************** Project: Robots and Household Financial Behavior *********************
***************************  Gomes, Jansson and Karabulut ***************************
*************** Identifying the Skill Transferability of Individuals ****************
****************************** Source: LINDA ****************************************
*************************************************************************************
** Stata Settings ***
cap clear all
macro drop _all
clear
set mem 1000m
set matsize 11000
set maxvar 10000
set more off
**Define Paths**
*cd "\\micro.intra\projekt\P0459$\P0459_gem\STATA\Third_project\20181108\data"
cd "\\micro.intra\projekt\P0459$\P0459_gem\SAS DATA\Thomas\Projekt2\bal"


foreach x in 1993 1999 {
clear
**Upload the Raw Data**
insheet using edu3_SNI_`x'_230423.csv, delim(",") clear
**Drop if there is no information on the level of education**
drop if mi(edulevel_1)==1
**Drop if there is no information on the industry of occupation
drop if mi(sni_2)==1|sni_2==0

/* 
Note: 
	There are a total of 58 different industries (+1 missing group) 
	There are a total of 24 different educational majors
	There are a total of 6 different educational level
*/

**Label the values of the educational major variable**
label define edumajor_3_l ///
10	"Broad, general education"	///
080	"Reading and writing for adults" ///
090	"Personal development" ///
140	"Education and teacher training, general education"	///
142	"Pedagogy"	///
143	"Teacher training for pre-school and leisure activities"	///
144	"Teacher education for compulsory school ages"	///
145	"Subject Teacher Training"	///
146	"Teacher training in vocational subject and practical / aesthetic subject"	///
149	"Education and teacher training, other / unspecified education"	///
210	"Arts and media, public education"	///
211	"Visual arts"	///
212	"Music, dance and drama"	///
213	"Media Production"	///
214	"Design"	///
215	"Crafts"	///
219	"Arts and media, other / unspecified education"	///
220	"Humanities, general orientation"	///
221	"Religion"	///
222	"Foreign Languages ​​and Linguistics"	///
223	"Swedish and literature"	///
225	"History and Archaeology"	///
226	"Philosophy and Logic"	///
229	"Humanities, other / unspecified orientation"	///
310	"Social and behavioral sciences, general orientation"	///
311	"Psychology"	///
312	"Sociology, ethnology and human geography"	///
313	"Political science"	///
314	"Economics and economic history"	///
319	"Social and behavioral sciences, other / unspecified orientation"	///
320	"Journalism and information, education"	///
321	"Journalism and media studies"	///
322	"Library and Documentation Science"	///
329	"Journalism and information, other / unspecified education"	///
340	"Business administration, commerce, administration, public educ."	///
341	"Purchasing, sales and distribution"	///
342	"Marketing"	///
343	"Banking, insurance and finance"	///
344	"Accounting and taxation"	///
345	"Management and administration"	///
346	"Office services and secretarial services"	///
347	"Workplace-related training"	///
349	"Business, trade and administration, other / unspecified educ."	///
380	"Law and Legal Studies"	///
420	"Biology and environmental sciences, general education"	///
421	"Biology and Biochemistry"	///
422	"Environmental Science"	///
429	"Biology and environmental sciences, other / unspecified education"	///
440	"Physics, chemistry and earth science, general education"	///
441	"Physics"	///
442	"Chemistry"	///
443	"Earth Science and Physical Geography"	///
449	"Physics, chemistry and earth sciences, other / unspecified education"	///
460	"Mathematics and science, general education"	///
461	"Mathematics"	///
462	"Statistics"	///
469	"Mathematics and science, other / unspecified education"	///
480	"Data, education"	///
481	"Informatics, Computer Science and Systems Sciences"	///
482	"Computing"	///
489	"Data, other / unspecified education"	///
520	"Engineering and engineering industries, general orientation"	///
521	"Engineering and Engineering Technology"	///
522	"Energy and electrical engineering"	///
523	"Electronics, information technology and automation"	///
524	"Chemical and Biological Engineering"	///
525	"Automotive and Vehicle Engineering"	///
526	"Industrial Economics and Management"	///
529	"Engineering and engineering industries, other / unspecified orientation"	///
540	"Materials and manufacturing, general orientation"	///
541	"Manufacturing and handling of food"	///
542	"Production of textiles, clothing and leather goods	"	///
543	"Production of wood, paper, glass / porcelain and plastic products"	///
544	"Mining and Mineral Engineering"	///
549	"Manufacturing, other / unspecified orientation"	///
580	"Planning and building technology, general orientation"	///
581	"Civil and Architecture"	///
582	"Construction and civil engineering"	///
589	"Planning and building technology, other / unspecified orientation"	///
620	"Agriculture, horticulture, forestry and fishing, public education"	///
621	"Agriculture"	///
622	"Garden"	///
623	"Forest"	///
624	"Fisheries and aquaculture"	///
629	"Agriculture, horticulture, forestry and fisheries, other / unspecified education"	///
640	"Veterinary care"	///
720	"Health and education"	///
721	"Medicine"	///
723	"Nursing"	///
724	"Dentistry"	///
725	"Technically oriented health education"	///
726	"Therapy, rehabilitation and dietary treatment"	///
727	"Pharmacy"	///
729	"Health care, other / unspecified education"	///
760	"Social work and social care, education"	///
761	"Children and Youth"	///
762	"Social work and counseling"	///
769	"Social work and social care, other / unspecified education"	///
810	"Personal services, education"	///
811	"Hotel, restaurant and catering"	///
812	"Tourism, travel and leisure"	///
813	"Sports and wellness"	///
814	"Domestic services and cleaning"	///
815	"Hair and beauty"	///
819	"Personal services, other / unspecified education"	///
840	"Transportation"	///
850	"Environmental protection, education"	///
851	"Environmental Engineering and environmental control"	///
852	"Conservation and Protection"	///
853	"Sanitation and waste management"	///
859	"Environmental protection, other / unspecified education"	///
860	"Security, education"	///
861	"Security and Public Safety"	///
862	"Work environment and safety"	///
863	"Military training"	///
869	"Security services, other / unspecified education"	///
999	"Notorious"	
label values edumajor_3 edumajor_3_l

**Label the values of the industry of occupation variable**
label define sni_2_l ///
 01 "Agriculture, hunting and related service activities" ///
02 "Forestry, logging and related service activities" ///
05 "Fishing, operation of fish hatcheries and fish farms; service activities incidental to fishing" ///
10 "Mining of coal and lignite; extraction of peat" ///
11 "Extraction of crude petroleum and natural gas; service activities incidental to oil and gas extraction excluding surveying" ///
12 "Mining of uranium and thorium ores" ///
13 "Mining of metal ores" ///
14 "Other mining and quarrying" ///
15 "Manufacture of food products and beverages" ///
16 "Manufacture of tobacco products" ///
17 "Manufacture of textiles" ///
18 "Manufacture of wearing apparel; dressing and dyeing of fur" ///
19 "Tanning and dressing of leather; manufacture of luggage, handbags, saddlery, harness and footwear" ///
20 "Manufacture of wood and of products of wood and cork, except furniture; manufacture of articles of straw and plaiting materials" ///
21 "Manufacture of pulp, paper and paper products"  ///
22 "Publishing, printing and reproduction of recorded media" ///
23 "Manufacture of coke, refined petroleum products and nuclear fuel" ///
24 "Manufacture of chemicals and chemical products" ///
25 "Manufacture of rubber and plastic products" ///
26 "Manufacture of other non-metallic mineral products" ///
27 "Manufacture of basic metals" ///
28 "Manufacture of fabricated metal products, except machinery and equipment" ///
29 "Manufacture of machinery and equipment n.e.c." ///
30 "Manufacture of office machinery and computers" ///
31 "Manufacture of electrical machinery and apparatus n.e.c." ///
32 "Manufacture of radio, television and communication equipment and apparatus" ///
33 "Manufacture of medical, precision and optical instruments, watches and clocks" ///
34 "Manufacture of motor vehicles, trailers and semi-trailers" ///
35 "Manufacture of other transport equipment" ///
36 "Manufacture of furniture; manufacturing n.e.c." ///
37 "Recycling" ///
40 "Electricity, gas, steam and hot water supply" ///
41 "Collection, purification and distribution of water" ///
45 "Construction" ///
50 "Sale, maintenance and repair of motor vehicles and motorcycles; retail sale of automotive fuel" ///
51 "Wholesale trade and commission trade, except of motor vehicles and motorcycles" ///
52 "Retail trade, except of motor vehicles and motorcycles; repair of personal and household goods" ///
55 "Hotels and restaurants" ///
60 "Land transport; transport via pipelines" ///
61 "Water transport" ///
62 "Air transport" ///
63 "Supporting and auxiliary transport activities; activities of travel agencies" ///
64 "Post and telecommunications" ///
65 "Financial intermediation, except insurance and pension funding" ///
66 "Insurance and pension funding, except compulsory social security" ///
67 "Activities auxiliary to financial intermediation" ///
70 "Real estate activities" ///
71 "Renting of machinery and equipment without operator and of personal and household goods" ///
72 "Computer and related activities" ///
73 "Research and development" ///
74 "Other business activities" ///
75 "Public administration and defence; compulsory social security" ///
80 "Education" ///
85 "Health and social work" ///
90 "Sewage and refuse disposal, sanitation and similar activities" ///
91 "Activities of membership organizations n.e.c." ///
92 "Recreational, cultural and sporting activities" ///
93 "Other service activities" ///
95 "Activities of households as employers of domestic staff" ///
99 "Extra-territorial organizations and bodies" 
label value sni_2 sni_2_l
**Calculate the total number of people in each educational major group**
bys edumajor_3: egen tot_nr_edu3=total(count)
**Aggregate the education level in two groups: high vs. low**
gen high_edu=1 if (edulevel_1==4|edulevel_1==5|edulevel_1==6)
replace high_edu=0 if (edulevel_1==1|edulevel_1==2|edulevel_1==3)
**Calculate the total number of people in each educatonal major-level group**
bys edumajor_3 high_edu: egen tot_nr_edu3_lev=total(count)
**Calculate the total number of people in each educatonal major-level-industry group**
bys edumajor_3 high_edu sni_2: egen tot_edu3_lev_sni=total(count)
**Duplicates drop
duplicates drop edumajor_3 high_edu sni_2, force
**Drop variables of no interest
drop edulevel_1 percent count
**Calculate the HHI Index
**First calculate the HHI manually
egen prop=pc(tot_edu3_lev_sni), by(edumajor_3 high_edu)
replace prop=(prop/100)
egen HHI=total(prop^2), by(edumajor_3 high_edu)
tabdisp high_edu edumajor_3,c(HHI) nol
**Second calculate the HHI "automatically"
hhi tot_edu3_lev_sni, by(edumajor_3 high_edu)
**Check the consisntecy the HHi variables
su HHI hhi_tot_edu3_lev_sni
**Keep the Educational Major - Educational Level
duplicates drop edumajor_3 high_edu, force
**Keep the relevant variables**
keep edumajor_3 high_edu HHI 
**Generate an ID for the HHi from lowest to highest values**
sort HHI
gen hhi_id=_n
**Save the data**
save "\\micro.intra\projekt\P0459$\P0459_gem\STATA\Third_project\20181108\data\transferability_index_edu3_final_`x'.dta", replace
}

global projf "\\micro.intra\projekt\P0459$\P0459_gem\STATA\Third_project\20181108"

**Upload the skill transferability data - 1993**
use "$projf\data\transferability_index_edu3_final_1993.dta", clear
sum HHI, d
sum hhi_id, d
sort high_edu edumajor_3
rename high_edu college 
rename edumajor_3 edumajor_3_93
save "$projf\data\transferability_index_edu3_93_230423.dta", replace

*cf _all using "\\micro.intra\projekt\P0459$\P0459_gem\STATA\Third_project\20181108\data\transferability_index_edu3_1993_final.dta", all


**Upload the skill transferability data - 1999**
use "$projf\data\transferability_index_edu3_final_1999.dta", clear
sum HHI, d
sum hhi_id, d
sort high_edu edumajor_3
rename high_edu college 
rename edumajor_3 edumajor_3_99
save "$projf\data\transferability_index_edu3_230423.dta", replace


*cf _all using "\\micro.intra\projekt\P0459$\P0459_gem\STATA\Third_project\20181108\data\transferability_index_edu3.dta", all



